/*
 * Copyright 2011-2012 The ProjectSample Organization
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.projectsample.simplest.s1.security;

import java.sql.SQLException;

import org.projectsample.simplest.s1.exception.DbException;
import org.projectsample.simplest.s1.security.utils.MD5;
import org.projectsample.simplest.dbutils.Session;
import org.projectsample.simplest.dbutils.SessionFactory;

/**
 * @author Jason Xing
 */
public class Initialize {
    
    public void createDb() {
        String sql = "";
        Session session = null;
        try {
            session = SessionFactory.openSession();
            // 资源
            sql = "create table resource (                    \n" +
                  "    id              integer not null generated always as identity primary key, \n" +
                  "    type            varchar(16) not null,  \n" +
                  "    name            varchar(64) not null,  \n" +
                  "    value           varchar(128),          \n" +
                  "    unique (type, name)                    \n" +
                  ")                                          \n";
            session.execute(sql);
            // 菜单（前台）。resource的一种，继承自resource。
            sql = "create table menu (                               \n" +
                  "    id              integer not null primary key references resource (id), \n" +
                  "    parent_id       integer references menu (id), \n" +
                  "    \"order\"         integer not null,           \n" +
                  "    is_leaf         boolean not null              \n" +
                  ")                                                 \n";
            session.execute(sql);
            // 权限
            sql = "create table permission (                        \n" +
                  "    id              integer not null generated always as identity primary key, \n" +
                  "    name            varchar(64) not null unique, \n" +
                  "    resource_id     integer not null references resource (id),                 \n" +
                  "    operation       varchar(16) not null,        \n" +
                  "    unique (resource_id, operation)              \n" +
                  ")                                                \n";
            session.execute(sql);
            // 角色
            sql = "create table role (                              \n" +
                  "    id              integer not null generated always as identity primary key, \n" +
                  "    name            varchar(64) not null unique, \n" +
                  "    parent_id       integer references role (id) \n" +
                  ")                                                \n";
            session.execute(sql);
            // 角色的权限
            sql = "create table role_permission (      \n" +
                  "    role_id         integer not null references role (id),       \n" +
                  "    permission_id   integer not null references permission (id), \n" +
                  "    unique (role_id, permission_id) \n" +
                  ")                                   \n";
            session.execute(sql);
            // 用户。user在DB中是关键字
            sql = "create table \"user\" (                          \n" +
                  "    id              integer not null generated always as identity primary key, \n" +
                  "    username        varchar(32) not null unique, \n" +
                  "    password_enc    varchar(32) not null,        \n" +
                  "    email           varchar(32) unique           \n" +
                  ")                                                \n";
            session.execute(sql);
            // 用户的角色
            sql = "create table user_role (      \n" +
                  "    user_id         integer not null references \"user\" (id), \n" +
                  "    role_id         integer not null references role (id),     \n" +
                  "    unique (user_id, role_id) \n" +
                  ")                             \n";
            session.execute(sql);
            session.commit();
        } catch (SQLException e) {
            session.rollback();
            throw new DbException(e);
        } finally {
            session.close();
        }
    }

    public void initData() {
        Session session = null;
        try {
            session = SessionFactory.openSession();
            String insertResource = "insert into resource (type, name, value) values (?, ?, ?)";
            String getMaxIdResource = "select max(id) from resource";
            String insertMenu = "insert into menu (id, parent_id, \"order\", is_leaf) values (?, ?, ?, ?)";
            String insertPermission = "insert into permission (name, resource_id, operation) values (?, ?, ?)";
            String getMaxIdPermission = "select max(id) from permission";
            String insertRole = "insert into role (name, parent_id) values (?, ?)";
            String getMaxIdRole = "select max(id) from role";
            String insertRolePermission = "insert into role_permission (role_id, permission_id) values (?, ?)";
            String getMaxIdUser = "select max(id) from \"user\"";

            // 插入根菜单
            session.execute(insertResource, "MENU", "Root", null);
            Integer rootMenuId = session.getValue(getMaxIdResource);
            session.execute(insertMenu, rootMenuId, null, 1, false);

            // 插入UserLogin URL
            session.execute(insertResource, "URL", "UserLogin", "/UserLogin.action");
            Integer userLoginUrlId = session.getValue(getMaxIdResource);
            // 插入UserLogin URL权限
            session.execute(insertPermission, "UserLogin", userLoginUrlId, "VISIT");
            Integer userLoginPermissionId = session.getValue(getMaxIdPermission);
            // 插入UserRegister URL
            session.execute(insertResource, "URL", "UserRegister", "/UserRegister.action");
            Integer userRegisterUrlId = session.getValue(getMaxIdResource);
            // 插入UserRegister URL权限
            session.execute(insertPermission, "UserRegister", userRegisterUrlId, "VISIT");
            Integer userRegisterPermissionId = session.getValue(getMaxIdPermission);
            // 插入UserRegisterEnter URL
            session.execute(insertResource, "URL", "UserRegisterEnter", "/UserRegisterEnter.action");
            Integer userRegisterEnterUrlId = session.getValue(getMaxIdResource);
            // 插入UserRegisterEnter URL权限
            session.execute(insertPermission, "UserRegisterEnter", userRegisterEnterUrlId, "VISIT");
            Integer userRegisterEnterPermissionId = session.getValue(getMaxIdPermission);
            
            // 插入基础角色（根角色）
            session.execute(insertRole, "BaseRole", null);
            Integer baseRoleId = session.getValue(getMaxIdRole);
            // 插入普通角色
            session.execute(insertRole, "CommonRole", baseRoleId);
            // 插入管理员角色
            session.execute(insertRole, "Administrator", null);
            Integer administratorRoleId = session.getValue(getMaxIdRole);
            // 插入匿名者角色
            session.execute(insertRole, "Anonymous", null);
            Integer anonymousRoleId = session.getValue(getMaxIdRole);
            // 为匿名者角色分配UserLogin URL权限
            session.execute(insertRolePermission, anonymousRoleId, userLoginPermissionId);
            // 为匿名者角色分配UserRegister URL权限
            session.execute(insertRolePermission, anonymousRoleId, userRegisterPermissionId);
            // 为匿名者角色分配UserRegisterEnter URL权限
            session.execute(insertRolePermission, anonymousRoleId, userRegisterEnterPermissionId);
            
            // 插入管理员用户
            String adminUserName = "admin";
            String insertUser = "insert into \"user\" (username, password_enc, email) values (?, ?, ?)";
            session.execute(insertUser, adminUserName, MD5.encodeString("123456", null), "jasons.xing@gmail.com");
            Integer administratorUserId = session.getValue(getMaxIdUser);
            // 为管理员用户挂上管理员角色
            String insertUserRole = "insert into user_role (user_id, role_id) values (?, ?)";
            session.execute(insertUserRole, administratorUserId, administratorRoleId);
            
            session.commit();
        } catch (SQLException e) {
            session.rollback();
            throw new DbException(e);
        } finally {
            session.close();
        }
    }
}
